﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Sunny.UI;

namespace RclSystem
{
    public partial class Salarys : UIPage
    {
        Dictionary<string, SalaryVo> hashTable = new Dictionary<string, SalaryVo>();
        public Salarys()
        {
            InitializeComponent();
        }

        private void queryAccounts()
        {
            hashTable = new Dictionary<string, SalaryVo>();
            uiDataGridView1.Rows.Clear();
            
            //获取发货单列表
            string extsql = "";
            if(txt_saleman.Text.Trim() != "")
            {
                extsql += " and salesman = '" + txt_saleman.Text.Trim() + "'";
            }
            if(txt_company.Text.Trim() != "")
            {
                extsql += " and companyname like '%" + txt_company.Text.Trim() + "%'";
            }
            DataTable dt_deliver = MySQLHelper.ExecuteQuery("SELECT * FROM tbl_delivery where deliverdate >= '" + dt_qstart.Value.ToString("yyyy-MM-dd")
                + "' and deliverdate <= '" + dt_qend.Value.ToString("yyyy-MM-dd") + "'" + extsql + " order by companyname,deliverdate");

            if (dt_deliver.Rows.Count > 0)
            {
                foreach (DataRow row in dt_deliver.Rows)
                {
                    if (hashTable.ContainsKey(row["companyname"].ToString()))
                    {
                        SalaryVo vo = hashTable[row["companyname"].ToString()];
                        if (vo.salesman.IndexOf(row["salesman"].ToString()) > 0)
                        {
                            vo.salesman = vo.salesman + "," + row["salesman"].ToString();
                        }
                        vo.count += double.Parse(row["salecount"].ToString());
                        vo.nprice += double.Parse(row["saleamount"].ToString());
                        vo.bsalary += double.Parse(row["bsalary"].ToString());
                        vo.commission += double.Parse(row["settlement"].ToString());
                        vo.acceptance += double.Parse(row["interestsubsidy"].ToString());
                        vo.etc += double.Parse(row["tcamount"].ToString());
                        hashTable[row["companyname"].ToString()] = vo;
                    }
                    else
                    {
                        SalaryVo vo = new SalaryVo();
                        vo.companyname = row["companyname"].ToString();
                        vo.salesman = row["salesman"].ToString();
                        vo.count = double.Parse(row["salecount"].ToString());
                        vo.nprice = double.Parse(row["saleamount"].ToString());
                        vo.bsalary = double.Parse(row["bsalary"].ToString());
                        vo.commission = double.Parse(row["settlement"].ToString());
                        vo.acceptance = double.Parse(row["interestsubsidy"].ToString());
                        vo.etc = double.Parse(row["tcamount"].ToString());
                        //vo.receiptprice = double.Parse(dr["receiptamount"].ToString());
                        //vo.penalty = double.Parse(dr["interest"].ToString());
                        hashTable.Add(row["companyname"].ToString(), vo);
                    }
                }
            }
            //查询对应的收款单列表
            DataTable dt_receipt = MySQLHelper.ExecuteQuery("SELECT * FROM tbl_receipt where receiptdate >= '" + dt_qstart.Value.ToString("yyyy-MM-dd")
            + "' and receiptdate <= '" + dt_qend.Value.ToString("yyyy-MM-dd") + "'" + extsql + " order by companyname,receiptdate");

            if (dt_receipt.Rows.Count > 0)
            {
                foreach (DataRow row in dt_receipt.Rows)
                {
                    if (hashTable.ContainsKey(row["companyname"].ToString()))
                    {
                        SalaryVo vo = hashTable[row["companyname"].ToString()];
                        if (vo.salesman.IndexOf(row["salesman"].ToString()) > 0)
                        {
                            vo.salesman = vo.salesman + "," + row["salesman"].ToString();
                        }
                        vo.receiptprice += double.Parse(row["receiptamount"].ToString());
                        vo.penalty += double.Parse(row["interest"].ToString());
                        hashTable[row["companyname"].ToString()] = vo;
                    }
                    else
                    {
                        SalaryVo vo = new SalaryVo();
                        vo.companyname = row["companyname"].ToString();
                        vo.salesman = row["salesman"].ToString();
                        vo.receiptprice = double.Parse(row["receiptamount"].ToString());
                        vo.penalty = double.Parse(row["interest"].ToString());
                        hashTable.Add(row["companyname"].ToString(), vo);
                    }
                }
            }
            double count_total = 0;
            double deliverprice_total = 0;
            double receiptprice_total = 0;
            double bsalary_total = 0;
            double commission_total = 0;
            double penalty_total = 0;
            double acceptance_total = 0;
            double etc_total = 0;
            double total_total = 0;
            int index = 0;
            foreach (KeyValuePair<string, SalaryVo> kvp in hashTable)
            {
                //计算合计金额并显示
                kvp.Value.total = kvp.Value.bsalary + kvp.Value.commission - kvp.Value.penalty - kvp.Value.acceptance - kvp.Value.etc;
                index = this.uiDataGridView1.Rows.Add();
                this.uiDataGridView1.Rows[index].Cells[0].Value = index + 1;
                this.uiDataGridView1.Rows[index].Cells[1].Value = kvp.Value.companyname;
                this.uiDataGridView1.Rows[index].Cells[2].Value = kvp.Value.salesman;
                this.uiDataGridView1.Rows[index].Cells[3].Value = kvp.Value.count.ToString() == "0" ? "" : kvp.Value.count.ToString();
                this.uiDataGridView1.Rows[index].Cells[4].Value = kvp.Value.nprice.ToString() == "0" ? "" : kvp.Value.nprice.ToString();
                this.uiDataGridView1.Rows[index].Cells[5].Value = kvp.Value.receiptprice.ToString() == "0" ? "" : kvp.Value.receiptprice.ToString();
                this.uiDataGridView1.Rows[index].Cells[6].Value = kvp.Value.bsalary.ToString() == "0" ? "" : kvp.Value.bsalary.ToString();
                this.uiDataGridView1.Rows[index].Cells[7].Value = kvp.Value.commission.ToString() == "0" ? "" : kvp.Value.commission.ToString();
                this.uiDataGridView1.Rows[index].Cells[8].Value = kvp.Value.penalty.ToString() == "0" ? "" : kvp.Value.penalty.ToString();
                this.uiDataGridView1.Rows[index].Cells[9].Value = kvp.Value.acceptance.ToString() == "0" ? "" : kvp.Value.acceptance.ToString();
                this.uiDataGridView1.Rows[index].Cells[10].Value = kvp.Value.etc.ToString() == "0" ? "" : kvp.Value.etc.ToString();
                this.uiDataGridView1.Rows[index].Cells[11].Value = kvp.Value.total.ToString() == "0" ? "" : kvp.Value.total.ToString();

                count_total += kvp.Value.count;
                deliverprice_total += kvp.Value.nprice;
                receiptprice_total += kvp.Value.receiptprice;
                bsalary_total += kvp.Value.bsalary;
                commission_total += kvp.Value.commission;
                penalty_total += kvp.Value.penalty;
                acceptance_total += kvp.Value.acceptance;
                etc_total += kvp.Value.etc;
                total_total += kvp.Value.total;
            }

            //生成合计行
            index = this.uiDataGridView1.Rows.Add();
            this.uiDataGridView1.Rows[index].Cells[0].Value = index + 1;
            this.uiDataGridView1.Rows[index].Cells[1].Value = "";
            this.uiDataGridView1.Rows[index].Cells[2].Value = "合计";
            this.uiDataGridView1.Rows[index].Cells[3].Value = Math.Round(count_total, 3);
            this.uiDataGridView1.Rows[index].Cells[4].Value = Math.Round(deliverprice_total, 2); 
            this.uiDataGridView1.Rows[index].Cells[5].Value = Math.Round(receiptprice_total, 2);
            this.uiDataGridView1.Rows[index].Cells[6].Value = Math.Round(bsalary_total, 2);
            this.uiDataGridView1.Rows[index].Cells[7].Value = Math.Round(commission_total, 2);
            this.uiDataGridView1.Rows[index].Cells[8].Value = Math.Round(penalty_total, 2);
            this.uiDataGridView1.Rows[index].Cells[9].Value = Math.Round(acceptance_total, 2);
            this.uiDataGridView1.Rows[index].Cells[10].Value = Math.Round(etc_total, 2);
            this.uiDataGridView1.Rows[index].Cells[11].Value = Math.Round(total_total, 2);
        }

        private void Salarys_Initialize(object sender, EventArgs e)
        {
            dt_qend.Value = DateTime.Now;
            dt_qstart.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
        }

        private void btn_query_Click(object sender, EventArgs e)
        {
            queryAccounts();
        }

        public void ExportDataToExcel()
        {
            string FileName = "工资单明细";
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            //设置文件标题
            saveFileDialog.Title = "导出Excel文件";
            //设置文件类型
            saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
            //设置默认文件类型显示顺序  
            saveFileDialog.FilterIndex = 1;
            //是否自动在文件名中添加扩展名
            saveFileDialog.AddExtension = true;
            //是否记忆上次打开的目录
            saveFileDialog.RestoreDirectory = true;
            //设置默认文件名
            saveFileDialog.FileName = FileName;
            //按下确定选择的按钮  
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                //获得文件路径 
                string localFilePath = saveFileDialog.FileName.ToString();

                //数据初始化

                //NPOI
                IWorkbook workbook;
                string FileExt = Path.GetExtension(localFilePath).ToLower();
                if (FileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (FileExt == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return;
                }
                ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);

                ICellStyle cellstyle = workbook.CreateCellStyle();
                cellstyle.VerticalAlignment = VerticalAlignment.Center;
                cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                sheet.SetColumnWidth(1, 30 * 256);
                sheet.SetColumnWidth(2, 12 * 256);
                sheet.SetColumnWidth(3, 12 * 256);
                sheet.SetColumnWidth(4, 12 * 256);
                sheet.SetColumnWidth(5, 12 * 256);
                sheet.SetColumnWidth(6, 12 * 256);
                sheet.SetColumnWidth(7, 12 * 256);
                sheet.SetColumnWidth(9, 12 * 256);
                sheet.SetColumnWidth(10, 12 * 256);
                sheet.SetColumnWidth(11, 12 * 256);

                try
                {
                    //读取标题  
                    int rowindex = 0;
                    IRow rowHeader = sheet.CreateRow(rowindex);
                    //添加标题
                    rowHeader.CreateCell(0).SetCellValue("序号");
                    rowHeader.CreateCell(1).SetCellValue("单位名称");
                    rowHeader.CreateCell(2).SetCellValue("业务员");
                    rowHeader.CreateCell(3).SetCellValue("发货数量");
                    rowHeader.CreateCell(4).SetCellValue("应收金额");
                    rowHeader.CreateCell(5).SetCellValue("收款金额");
                    rowHeader.CreateCell(6).SetCellValue("业务费金额");
                    rowHeader.CreateCell(7).SetCellValue("佣金金额");
                    rowHeader.CreateCell(8).SetCellValue("罚息金额");
                    rowHeader.CreateCell(9).SetCellValue("承兑贴息");
                    rowHeader.CreateCell(10).SetCellValue("ETC");
                    rowHeader.CreateCell(11).SetCellValue("合计金额");
                    
                    //读取数据  
                    int offset = 1;
                    foreach (KeyValuePair<string, SalaryVo> kvp in hashTable)
                    {
                        IRow rowData = sheet.CreateRow(++rowindex);
                        ICell cell = rowData.CreateCell(0);
                        cell.SetCellValue(offset++);

                        cell = rowData.CreateCell(1);
                        cell.SetCellValue(kvp.Value.companyname);

                        cell = rowData.CreateCell(2);
                        cell.SetCellValue(kvp.Value.salesman);

                        cell = rowData.CreateCell(3);
                        cell.SetCellValue(kvp.Value.count);

                        cell = rowData.CreateCell(4);
                        cell.SetCellValue(kvp.Value.nprice);

                        cell = rowData.CreateCell(5);
                        cell.SetCellValue(kvp.Value.receiptprice);

                        cell = rowData.CreateCell(6);
                        cell.SetCellValue(kvp.Value.bsalary);

                        cell = rowData.CreateCell(7);
                        cell.SetCellValue(kvp.Value.commission);

                        cell = rowData.CreateCell(8);
                        cell.SetCellValue(kvp.Value.penalty);

                        cell = rowData.CreateCell(9);
                        cell.SetCellValue(kvp.Value.acceptance);

                        cell = rowData.CreateCell(10);
                        cell.SetCellValue(kvp.Value.etc);

                        cell = rowData.CreateCell(11);
                        cell.SetCellValue(kvp.Value.total);
                    }                    

                    //转为字节数组  
                    MemoryStream stream = new MemoryStream();
                    workbook.Write(stream);
                    var buf = stream.ToArray();

                    //保存为Excel文件  
                    using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
                    {
                        fs.Write(buf, 0, buf.Length);
                        fs.Flush();
                        fs.Close();
                    }


                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        private void uiSymbolButton1_Click(object sender, EventArgs e)
        {
            ExportDataToExcel();
        }

        private void uiDataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (uiDataGridView1.Columns[e.ColumnIndex].Name == "btn_detail" && e.RowIndex >= 0)
            {
                DataGridViewRow vr = uiDataGridView1.Rows[e.RowIndex];
                string companyname = vr.Cells[1].Value.ToString();
                string salesman = vr.Cells[2].Value.ToString();
                double total = double.Parse(vr.Cells[11].Value.ToString());
                //明细对话框
                SalaryDetail sdetail = new SalaryDetail(companyname, salesman, total, dt_qstart.Value.ToString("yyyy-MM-dd"), dt_qend.Value.ToString("yyyy-MM-dd"));
                sdetail.ShowDialog();

            }
        }
    }
}
